package com.tuanzi.loan.business.repository.statistics.impl;

import com.tuanzi.loan.business.entity.collaborate.StatNewCollaborateRegister;
import com.tuanzi.loan.business.entity.family.RegisterRecommendActivity;
import com.tuanzi.loan.business.repository.statistics.CommActivityRegisterNewStatRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepositoryPlus;
import com.tuanzi.loan.vo.request.Collaborate.CollaborateStatListRequest;
import com.tuanzi.loan.vo.request.statistics.CommActivityRegisterStatRequest;
import com.tuanzi.loan.vo.response.collaborate.CollaborateStatListResponse;
import com.tuanzi.loan.vo.response.statistics.CommActivityRegisterStatResponse;
import com.tuanzi.server.loan_base.model.bean.common.CommPlatform;
import com.tuanzi.server.loan_base.model.db.CommProductChannel;
import com.tuanzi.server.loan_base.model.db.CommProductPrdid;
import com.tuanzi.server.loan_base.util.DateUtils;
import org.apache.commons.lang3.StringUtils;
import org.jooq.Condition;
import org.jooq.SelectField;
import org.jooq.SelectQuery;
import org.jooq.impl.DSL;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import java.util.*;

import static com.tuanzi.loan.generated.jooq.loan.tables.CommProductChannel.COMM_PRODUCT_CHANNEL;
import static com.tuanzi.loan.generated.jooq.loan.tables.CommProductPrdid.COMM_PRODUCT_PRDID;
import static com.tuanzi.loan.generated.jooq.loan.tables.RegisterRecommendActivity.REGISTER_RECOMMEND_ACTIVITY;
import static com.tuanzi.loan.generated.jooq.stat.tables.StatCommActivityRegisterNew.STAT_COMM_ACTIVITY_REGISTER_NEW;

@Repository
public class CommActivityRegisterNewStatRepositoryImpl extends JOOQBaseRepositoryPlus implements CommActivityRegisterNewStatRepository{

	private static Map<String,String> activityTypeMap = null;
	private static List<RegisterRecommendActivity> activityTypeList = null;
	private static List<CommProductPrdid> product;
	private static List<CommProductChannel> channel;
	private static List<CommPlatform> platform;
	private static Map<String,String> prodMap;
	private static Map<String,String> channelMap;

	@Override
	protected TableImpl getTable() {
		return STAT_COMM_ACTIVITY_REGISTER_NEW;
	}
	
	public void cleanCache() {
		activityTypeMap = null;
		activityTypeList = null;
		product = null;
		channel = null;
		platform = null;
		prodMap = null;
		channelMap = null;
	}
	
	public Map<String,String> getProdMap() {
		getProductList();
    	return prodMap;
    }
    
    public List<CommProductPrdid> getProductList(){
        if (Objects.isNull(product)){
        	prodMap = new HashMap<String, String>();
        	SelectQuery sql = dslContext.select(COMM_PRODUCT_PRDID.PRDID, COMM_PRODUCT_PRDID.NAME)
                    .from(COMM_PRODUCT_PRDID).orderBy(COMM_PRODUCT_PRDID.PRDID.asc()).getQuery();
        	product = sql.fetch(record -> {
        		CommProductPrdid prod = new CommProductPrdid();
        		prod.setPrdid(record.get(0,Integer.class));
        		prod.setName(record.get(1,String.class));
        		
        		prodMap.put(String.valueOf(prod.getPrdid()), prod.getName());
        		return prod;
        	});
        }
        return product;
    }

    public Map<String,String> getChannelMap() {
        getChannel();
        return channelMap;
    }

    public List<CommProductChannel> getChannel(){
        if (Objects.isNull(channel)){
        	channelMap = new HashMap<String, String>();
        	SelectQuery sql = dslContext.select(COMM_PRODUCT_CHANNEL.CHANNEL_CODE, COMM_PRODUCT_CHANNEL.CHANNEL_NAME)
                    .from(COMM_PRODUCT_CHANNEL).orderBy(COMM_PRODUCT_CHANNEL.CHANNEL_CODE.asc()).getQuery();
        	channel = sql.fetch(record -> {
        		CommProductChannel channel = new CommProductChannel();
        		channel.setChannel_code(record.get(0,Integer.class));
        		channel.setChannel_name(record.get(1,String.class));
        		channelMap.put(String.valueOf(channel.getChannel_code()), channel.getChannel_name());
        		return channel;
        	});
        }
        return channel;
    }

    public List<CommPlatform> getPlatform(){
        if (Objects.isNull(platform)){
        	platform = new ArrayList<CommPlatform> ();
        	platform.add(new CommPlatform("ios","IOS"));
        	platform.add(new CommPlatform("android","安卓"));
        }
        return platform;
    }
	
	public Map<String,String> getActivityTypeMap() {
		getActivityTypeList();
		return activityTypeMap;
	}
	
	public List<RegisterRecommendActivity> getActivityTypeList() {
		if(activityTypeList == null) {
			activityTypeMap = new HashMap<String,String>();
			SelectQuery sql = dslContext.select(REGISTER_RECOMMEND_ACTIVITY.ACTIVITY_TYPE, REGISTER_RECOMMEND_ACTIVITY.ACTIVITY_TYPE_NAME)
					.from(REGISTER_RECOMMEND_ACTIVITY).getQuery();
			activityTypeList = sql.fetch(record -> {
				RegisterRecommendActivity type = new RegisterRecommendActivity();
				type.setActivityType(record.get(0,String.class));
				type.setActivityTypeName(record.get(1,String.class));
				
				activityTypeMap.put(type.getActivityType(), type.getActivityTypeName());
				return type;
			});
		}
		return activityTypeList;
	}
	
	public Page<CommActivityRegisterStatResponse> getAllList(CommActivityRegisterStatRequest searchRequest) {
		SelectQuery selectQuery = getSelectQuery(searchRequest);
		List<CommActivityRegisterStatResponse> resultList = selectQuery.fetch(record -> {
			CommActivityRegisterStatResponse response = new CommActivityRegisterStatResponse();
			response.setCdate(record.get(0,Date.class));
			response.setProductName(getProdMap().get(record.get(1,String.class)));
			response.setChannelName(getChannelMap().get(record.get(2,String.class)));
			response.setPlatform(record.get(3,String.class));
			response.setActivityType(getActivityTypeMap().get(record.get(4,String.class)));
			response.setActivityId(record.get(5,String.class));
			String environment = record.get(6,String.class);
			if("1".equals(environment)) {
				response.setEnvironment("推广H5");
			} else if("2".equals(environment)) {
				response.setEnvironment("客户端");
			} else {
				response.setEnvironment("未知");
			}
			
			response.setExternal_register_count(record.get(7,Integer.class));
			response.setExternal_new_register_count(record.get(8,Integer.class));
			response.setApp_activate_count(record.get(9,Integer.class));
			response.setApp_activate_count_phone(record.get(10,Integer.class));
			response.setApi_order_count(record.get(11,Integer.class));
			response.setApi_order_success_count(record.get(12,Integer.class));
			response.setLoan_receipt_order_count(record.get(13,Integer.class));
			response.setLogin_count(record.get(14,Integer.class));
			return response;
		});
		return new PageImpl<CommActivityRegisterStatResponse>(resultList);
	}

	@Override
	public Page<CommActivityRegisterStatResponse> getList(
			CommActivityRegisterStatRequest searchRequest, Pageable pageable) { 
		SelectQuery selectQuery = getSelectQuery(searchRequest);
		return search(selectQuery, pageable, record ->{
			CommActivityRegisterStatResponse response = new CommActivityRegisterStatResponse();
			response.setCdate(record.get(0,Date.class));
			response.setProductName(getProdMap().get(record.get(1,String.class)));
			response.setChannelName(getChannelMap().get(record.get(2,String.class)));
			response.setPlatform(record.get(3,String.class));
			response.setActivityType(getActivityTypeMap().get(record.get(4,String.class)));
			response.setActivityId(record.get(5,String.class));
			String environment = record.get(6,String.class);
			if("1".equals(environment)) {
				response.setEnvironment("推广H5");
			} else if("2".equals(environment)) {
				response.setEnvironment("客户端");
			} else {
				response.setEnvironment("未知");
			}
			
			response.setExternal_register_count(record.get(7,Integer.class));
			response.setExternal_new_register_count(record.get(8,Integer.class));
			response.setApp_activate_count(record.get(9,Integer.class));
			response.setApp_activate_count_phone(record.get(10,Integer.class));
			response.setApi_order_count(record.get(11,Integer.class));
			response.setApi_order_success_count(record.get(12,Integer.class));
			response.setLoan_receipt_order_count(record.get(13,Integer.class));
			response.setLogin_count(record.get(14,Integer.class));
			return response;
		});
	}
	
	private SelectQuery getSelectQuery(CommActivityRegisterStatRequest searchRequest) {
		Condition condition = DSL.trueCondition();
        List<SelectField<?>> selectFields = new ArrayList<>();
        
        selectFields.add(STAT_COMM_ACTIVITY_REGISTER_NEW.CDATE);
        if (Objects.nonNull(searchRequest.getStartDate())) {
            condition = condition.and(STAT_COMM_ACTIVITY_REGISTER_NEW.CDATE.ge(new java.sql.Date(searchRequest.getStartDate().getTime())));
        }
        if (Objects.nonNull(searchRequest.getEndDate())) {
        	condition = condition.and(STAT_COMM_ACTIVITY_REGISTER_NEW.CDATE.le(new java.sql.Date(searchRequest.getEndDate().getTime())));
        }
        
        // 产品
        if (Objects.isNull(searchRequest.getProductId()) || searchRequest.getProductId().isEmpty()) {
        	// 全部展开
        	selectFields.add(STAT_COMM_ACTIVITY_REGISTER_NEW.PRD_ID);
        } else {
            selectFields.add(STAT_COMM_ACTIVITY_REGISTER_NEW.PRD_ID);
            condition = condition.and(STAT_COMM_ACTIVITY_REGISTER_NEW.PRD_ID.in(searchRequest.getProductId()));
        }
        
        // 渠道
        if (Objects.isNull(searchRequest.getChannelId()) || searchRequest.getChannelId().isEmpty()) {
        	// 全部展开
        	selectFields.add(STAT_COMM_ACTIVITY_REGISTER_NEW.CHANNEL);
        } else {
            selectFields.add(STAT_COMM_ACTIVITY_REGISTER_NEW.CHANNEL);
            condition = condition.and(STAT_COMM_ACTIVITY_REGISTER_NEW.CHANNEL.in(searchRequest.getChannelId()));
        }
        
        // 平台
        if (Objects.isNull(searchRequest.getPlatform()) || searchRequest.getPlatform().isEmpty()) {
        	// 全部展开
        	selectFields.add(STAT_COMM_ACTIVITY_REGISTER_NEW.PLATFORM);
        } else {
            selectFields.add(STAT_COMM_ACTIVITY_REGISTER_NEW.PLATFORM);
            condition = condition.and(STAT_COMM_ACTIVITY_REGISTER_NEW.PLATFORM.in(searchRequest.getPlatform()));
        }
        
        // 活动类型
        if (Objects.isNull(searchRequest.getActivityType()) || searchRequest.getActivityType().isEmpty()) {
        	// 全部展开
        	selectFields.add(STAT_COMM_ACTIVITY_REGISTER_NEW.ACTIVITY_TYPE);
        } else {
            selectFields.add(STAT_COMM_ACTIVITY_REGISTER_NEW.ACTIVITY_TYPE);
            condition = condition.and(STAT_COMM_ACTIVITY_REGISTER_NEW.ACTIVITY_TYPE.in(searchRequest.getActivityType()));
        }
        
        // 活动ID
        if (StringUtils.isEmpty(searchRequest.getActivityId())) {
        	// 全部展开
        	selectFields.add(STAT_COMM_ACTIVITY_REGISTER_NEW.ACTIVITY_ID);
        } else {
            selectFields.add(STAT_COMM_ACTIVITY_REGISTER_NEW.ACTIVITY_ID);
            condition = condition.and(STAT_COMM_ACTIVITY_REGISTER_NEW.ACTIVITY_ID.contains(searchRequest.getActivityId()));
        }
        
        // 请求环境
        if (StringUtils.isEmpty(searchRequest.getEnvironment())) {
        	// 全部展开
        	selectFields.add(STAT_COMM_ACTIVITY_REGISTER_NEW.ENVIRONMENT);
        } else {
            selectFields.add(STAT_COMM_ACTIVITY_REGISTER_NEW.ENVIRONMENT);
            condition = condition.and(STAT_COMM_ACTIVITY_REGISTER_NEW.ENVIRONMENT.eq(searchRequest.getEnvironment()));
        }
		
		return dslContext.select(selectFields)
        		.select(STAT_COMM_ACTIVITY_REGISTER_NEW.EXTERNAL_REGISTER_COUNT.as("external_register_count"))
                .select(STAT_COMM_ACTIVITY_REGISTER_NEW.EXTERNAL_NEW_REGISTER_COUNT.as("external_new_register_count"))
                .select(STAT_COMM_ACTIVITY_REGISTER_NEW.APP_ACTIVATE_COUNT.as("app_activate_count"))
                .select(STAT_COMM_ACTIVITY_REGISTER_NEW.APP_ACTIVATE_COUNT_PHONE.as("app_activate_count_phone"))
                .select(STAT_COMM_ACTIVITY_REGISTER_NEW.API_ORDER_COUNT.as("api_order_count"))
                .select(STAT_COMM_ACTIVITY_REGISTER_NEW.API_ORDER_SUCCESS_COUNT.as("api_order_success_count"))
                .select(STAT_COMM_ACTIVITY_REGISTER_NEW.LOAN_RECEIPT_ORDER_COUNT.as("loan_receipt_order_count"))
                .select(STAT_COMM_ACTIVITY_REGISTER_NEW.LOGIN_COUNT.as("login_count"))
                .from(STAT_COMM_ACTIVITY_REGISTER_NEW)
                .where(condition)
                .orderBy(STAT_COMM_ACTIVITY_REGISTER_NEW.CDATE.desc())
                .getQuery();
	}

	@Override
	public Page<CollaborateStatListResponse>  collaborateList(CollaborateStatListRequest searchRequest, Pageable pageable) {


		//当前时间
		String curDate = DateUtils.getCurrentDate();
		List<Integer> channelList = searchRequest.getChannelList();

		String join = StringUtils.join(channelList, ",");

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT a.* FROM ");
		sql.append("(SELECT cdate,prd_id,platform,channel,activity_id,external_register_count,external_new_register_count,app_activate_count,app_activate_count_phone");
		sql.append(" FROM stat.stat_comm_activity_register_new");
		sql.append(" WHERE cdate = '"+curDate+"'AND channel IN ("+join+")");
		sql.append(" UNION ALL");
		sql.append(" select cdate,prd_id,platform, channel,activity_id,external_register_count,external_new_register_count,app_activate_count,app_activate_count_phone from stat.stat_new_collaborate_register");
		sql.append(" where channel in("+join+" )) a where 1=1");
		//添加查询条件

		Date startDate = searchRequest.getStartDate();
		if(null != startDate){
			sql.append(" and a.cdate >=").append("'").append(DateUtils.formatDateToYyyyMMdd(startDate)).append("'");
		}

		Date endDate = searchRequest.getEndDate();
		if(null != endDate){
			sql.append(" and a.cdate <=").append("'").append(DateUtils.formatDateToYyyyMMdd(endDate)).append("'");
		}

		String platform = searchRequest.getPlatform();
		if(StringUtils.isNotBlank(platform)){
			sql.append(" and a.platform='").append(platform).append("'");
		}

		String activityId = searchRequest.getActivityId();
		if(StringUtils.isNotBlank(activityId)){
			sql.append(" and a.activity_id='").append(activityId).append("'");
		}

		String channelId = searchRequest.getChannelId();
		if(StringUtils.isNotBlank(channelId)){
			sql.append(" and a.channel='").append(channelId).append("'");
		}

		String productId = searchRequest.getProductId();
		if(StringUtils.isNotBlank(productId)){
			sql.append(" and a.prd_id='").append(productId).append("'");
		}

		sql.append(" ORDER BY a.cdate desc");

		String querySql = sql + " LIMIT ?,?";

		//查询数量sql
		String countSql = "SELECT COUNT(*) AS num FROM (" + sql.toString() + ")d";

		Object[] params= {pageable.getOffset(), pageable.getPageSize()};

		Long count = dslContext.fetchOne(countSql, params).into(Long.class);

		List<CollaborateStatListResponse> list = dslContext.fetch(querySql, params).map(record -> {
			CollaborateStatListResponse response = new CollaborateStatListResponse();
			response.setDate(record.get("cdate", Date.class));
			response.setProductId(record.get("prd_id", String.class));
			response.setChannelId(record.get("channel", String.class));
			response.setPlatform(record.get("platform", String.class));
			response.setActivityId(record.get("activity_id", String.class));
			response.setExternalRegisterCount(record.get("external_register_count", Integer.class));
			response.setExternalNewRegisterCount(record.get("external_new_register_count", Integer.class));
			response.setAppActivateCount(record.get("app_activate_count", Integer.class));
			response.setAppActivateCountPhone(record.get("app_activate_count_phone", Integer.class));
			return response;
		});

		return new PageImpl<>(list, pageable, count);
	}

	@Override
	public List<StatNewCollaborateRegister> findByChannelInAndDate(List<Integer> channelList, Date date) {
    	fields(STAT_COMM_ACTIVITY_REGISTER_NEW.fields());
		eq(STAT_COMM_ACTIVITY_REGISTER_NEW.CDATE, date);
		in(STAT_COMM_ACTIVITY_REGISTER_NEW.CHANNEL, channelList);
		return search(StatNewCollaborateRegister.class);
	}
}
























